from _ast import List

from app.services.business.gaolu_diagnose.ind.abstract_ind import AbstractInd


######该字段需要用到3个值参与计算
class CG_LT_GL_GL04_Rquanjiaofuhe(AbstractInd):

    def get_name(self):
        return "CG_LT_GL_GL04_Rquanjiaofuhe"

    def get_data_source(self):
        return "mysql"

    def get_sql(self, params):

        sql_kuanghao = """
            SELECT
            SUM(syn_tq_mbom1.fqty) as kuanghao,
            syn_tq_mbom1.fdate
            FROM
            syn_tq_mbom1
            INNER
            JOIN
            syn_tq_mmain
            ON
            syn_tq_mbom1.fitemid = syn_tq_mmain.fitemid
            WHERE
            syn_tq_mbom1.finsid = 8 and ftypeb = 1
            AND
            syn_tq_mbom1.fdate = DATE_SUB(CURDATE(), INTERVAL
            1
            DAY)
            GROUP
            BY
            syn_tq_mbom1.fdate
            DESC
            limit
            1
    
            """.format(**params)


        sql_tieliang = """
        
            SELECT
            SUM(tieliang) as CG_LT_GL_GL04_RChanliang
            FROM(
                SELECT
            t1.fnumber,
            t1.fdate,
            t2.tieliang
            FROM
            (SELECT
            cg.syn_tq_rgaoluchutie.fnumber,
            cg.syn_tq_rgaoluchutie.fdate
            FROM
            cg.syn_tq_rgaoluchutie
            where
            fdate = DATE_SUB(CURDATE(), INTERVAL
            1
            DAY))t1  
            JOIN
            (SELECT
            fnumber,
            SUM(fquantity) as tieliang
            FROM
            cg.syn_tq_rtieguan
            where
            CAST(fdatetime as date) > DATE_SUB(CURDATE(), INTERVAL
            2
            DAY) 
            GROUP
            BY
            fnumber)t2
            on
            t1.fnumber = t2.fnumber)t3
        
            """.format(**params)


        sql_quanjiao = """
            SELECT
            fdate,
            ROUND(AVG(fcokerate), 2) as quanjiao
            FROM
            syn_tq_rshangliaoqk
            where
            fdate = DATE_SUB(CURDATE(), INTERVAL
            1
            DAY)
            GROUP
            BY
            fdate
            DESC
        """.format(**params)

        return [sql_kuanghao, sql_tieliang, sql_quanjiao]

    @AbstractInd.cache_wrapper
    def get_result(self):
        sql_list: List[str] = self.get_sql(self.params)
        cursor = self.mysql_cursor
        #####字段1
        kuanghao_sql = sql_list[0]
        cursor.execute(kuanghao_sql)
        kuanghao_result = cursor.fetchone()
        kuanghao= float(kuanghao_result['kuanghao']) if kuanghao_result is not None else 0
        #####字段2

        tieliang_sql = sql_list[1]
        cursor.execute(tieliang_sql)
        tieliang_result = cursor.fetchone()
        tieliang = float(tieliang_result['CG_LT_GL_GL04_RChanliang']) if tieliang_result is not None else 0

        #####字段3
        quanjiao_sql = sql_list[2]
        cursor.execute(quanjiao_sql)
        quanjiao_result = cursor.fetchone()
        quanjiao = float(quanjiao_result['quanjiao']) if quanjiao_result is not None else 0

        if tieliang == 0 or kuanghao == 0 or quanjiao==0: return 0

        return round(kuanghao/tieliang /quanjiao*1000, 2)


